Cloud Spanner

In the last lesson, we saw how Cloud SQL can be used for lifting and shifting the existing on-premises relational database server. We have also learnt about the limitation of CloudSQL. So, in this lesson, we will look at Cloud Spanner which is the upgrade to the CloudSQL. Cloud Spanner combines the best of relational DB’s feature with the benefits of sharding and horizontal scaling. So, let’s start exploring Cloud Spanner.

Introduction#

Cloud Spanner is a fully managed relational database with unlimited scale, strong consistency, and up to 99.999% availability. Unlike CloudSQL, Cloud Spanner provides an in-browser query interface where you can query data.

Cloud Spanner is much like CloudSQL in most of the aspects however, the Spanner is more powerful. So, let’s see how exactly Cloud Spanner works.

Architecture and working#

Behind the scenes, Spanner separates the compute and storage and allow them to scale independently. From the compute and storage, we have control over compute power meaning we can select how much compute power we need by providing the number of instances.

Then, these instances and the data is replicated across regions to provide high availability. Keep in mind that currently, replication is done for a maximum of 4 regions.

We can increase the compute power at any time just by increasing the number of instances.

Advantages#

The advantages of Cloud Spanner are:

• You get all the benefits of relational semantics and SQL with unlimited scale.

• You can start at any size and scale with no limits and no downtime as your needs grow.

• Enjoy high availability with zero scheduled downtime and online schema changes.

• You can deliver high-performance transactions with strong consistency across regions and continents.

• Cloud Spanner provides automatic sharding, so there is no manual work for enhancement.

Let’s see where Cloud Spanner is the right fit.

Usecases#

You will use Cloud Spanner whenever you:

• Require an RDBMS platform with strong ACID properties on a global scale.

• Start with what you have and scale as you go without any extra manual operations.

• Need general-purpose RDBMS with support for high Queries per second on 10TBs or more data.

• Want to minimize the cost of ownership at the enterprise level. Because with Spanner you don’t have to manage high availability replica and read replicas.

• Need an online schema generator that can alter schema with minimal time depending upon the alteration. (Creating a new index on a large dataset will take some time.)

For the exam, whenever you see the words, high availability, Strong Consistency at Global Scale, RDBMS and Horizontal Scaling Cloud Spanner is the way to go.

Demo#

Let’s create the Cloud Spanner instance. That way you will understand the exact working of Cloud Spanner. In this demo, we will create a compute instance to process the data. Then we will create the database and finally, we will query the tables. So, let’s start.

To create the spanner instance,

Go to the Main menu> Databases > Spanner.

Creating instances#

Once you click on the spanner, you will see the home screen for the Spanner. Click on Create Instance to create the instance. A form to create the instance will open up. The inputs you need to provide are:

• Instance name: A string with max 30 character. Enter the name cloud spanner demo as the instance name.

• Instance ID: This is an auto-generated ID. You can modify this, but it needs to be unique in the project. Let’s go with an auto-generated one.

• Region Configuration: As cloud spanner provides highly available nodes, we can select the regions where they can be available. The configuration differs for regional and multi-regional instances. Toggle the radio option of regional and multi-regional to see the changes. Below is the configuration difference for regional(asia-east2) and multi-regional (nam8) instance configuration.

• Next, provide the number of nodes. Keep it 1 for now.

  Click the CREATE button.

Creating a database#

Once the instance is up and running we can then create our database. The database will be simple. We will create a database named Vehicle. It will have 2 tables Cars and Bikes.

The tables can be added at the time of database creation or later on. The only difference would be when we create tables at the time of the database creation we can only use SQL DDL statements. But if we add tables later on we can use dialogues to specify table schema. So, let’s start.

• Go to instance dashboard. Click on the Create Database button.

• Name the database as vehicle. Skip the schema and click the “CREATE” button.

Once the database is created next step is to add tables to the database.

Adding tables#

On the instance dashboard page, the newly created database will be displayed under the databases section. Click on the vehicle database. On the database overview page, there will be multiple ways to load data into the database.

1. Using Import/Export button to import data.

2. Using the Backup/restore button to restore the previous data.

3. Using Query option to write SQL insert queries to insert tables and data.

But we will use the GUI method. So, click the CREATE TABLE button. A form to create a table will open up. Copy and paste the following DDL statements to create the cars table.

CREATE TABLE `cars` (
    `id` INT64 NOT NULL,
    `name` STRING(MAX),
) PRIMARY KEY (id)

You can also toggle the Edit as text button to access the form view to add columns and the primary key for the table. Click the CREATE button to create the table. Create the bike table in the same way by copy and paste the following DDL statements.

CREATE TABLE `bike` (
    `id` INT64 NOT NULL,
    `name` STRING(MAX),
) PRIMARY KEY (id)

Inserting and modifying data#

To insert data into the newly created tables, click on the table name.

• In the left pane menu, click on the Data.

• Click on the INSERT button. A form with all the fields of the table will open up. Provide values for each field and click save.

• The inserted row will list down under the data section. Select the row and option to EDIT and DELETE the row will get activated.

You can also delete the whole table by clicking the DELETE TABLE button in the top right corner.

Querying the database#

Cloud Spanner also provides multiple ways to query the database. It has dedicated client libraries for each language. It also provides the in-browser query builder interface to query the database. We will use the in-browser query builder.

• Click on the vehicle database under the database section on the cloud spanner instance dashboard.

• In the left pane, click the Query button. This will open the query interface of the cloud spanner. The query interface allows only single statement queries.

  Let’s run some queries.

• Query to select name of all available bikes.

SELECT name from bike;

2. Since there is no data in the cars table, insert some data in the table using the below query.

INSERT INTO
  `cars` (id,name)
VALUES
  (1,"BMW");

gcloud CLI#

GCP also provides gcloud CLI utility to work with cloud spanner.

• To list the instances type gcloud spanner instances list

• To list the databases inside the instances type, gcloud spanner databases list --instance [INSTANCE ID]

• To delete the instance, type gcloud spanner instances delete [Instance ID]

  The gcloud spanner databases --help command will list all the operations you can perform using the gcloud sdk on spanner databases and the gcloud spanner instances --help command will list all the operations that can be performed on spanner instances.

Cleaning up#

Once the demo is over, do not forget to delete the instance otherwise if you forget to delete the instance then after 100 hours you will exhaust the free credits. Also, you might get charged for the remaining hours if spanner instances are still up and running after exhausting the credits.

You can delete the instance using GUI or CLI.

1. Using GUI, Go to the instance overview page by clicking on the instance name and then click the DELETE INSTANCE button in the upper right corner.
2. Using CLI, Type the command gcloud spanner instances delete [Instance ID] with the instance ID. In this case, the instanceID will be cloud-spanner-demo.

Conclusion#

Cloud Spanner is the best choice if you have global traffic and need an RDBMS which can reliably support all the load across regions. For this course, this is more than enough to know about creating and using the spanner. You can explore more about spanner from documentation.